#!/usr/bin/python
#coding=utf-8

from sqlalchemy import create_engine
from sqlalchemy.types import VARCHAR,INTEGER,DECIMAL
import pandas as pd
from sqlalchemy.pool import NullPool
from common import db_utils as util
from common import df_operator as op
import sys

dtypedict = {'ACCT': INTEGER,
            'COST_CENTER': VARCHAR,'UNIT_CODE': VARCHAR,'CLASS': VARCHAR,'SHIFT': VARCHAR,
            'PRODUCE_TIME': VARCHAR,'PROD_COILNO': VARCHAR,'ENTRY_COILNO': VARCHAR,'ST_NO': VARCHAR,
            'ACT_TOTAL_COST': DECIMAL,'MAIN_MATERIAL_COST': DECIMAL,'ENERGYCONSUM_COST': DECIMAL,
            'ACCES_COST': DECIMAL,'ROLL_COST': DECIMAL,'OTHER_COST': DECIMAL,
            'COST_6': DECIMAL,'COST_7': DECIMAL,'COST_8': DECIMAL,
            'ACT_OUTPUT_WT': DECIMAL,'ACT_INPUT_WT': DECIMAL,'THICK': VARCHAR,'THICK_CODE': VARCHAR,
            'WIDTH': VARCHAR, 'WIDTH_CODE': VARCHAR, 'ANNEAL_CURVE': VARCHAR, 'TRIM_FLAG': VARCHAR,
            'ENTRY_WIDTH': VARCHAR, 'ENTRY_WIDTH_CODE': VARCHAR, 'ENTRY_THICK': VARCHAR, 'ENTRY_THICK_CODE': VARCHAR,
            'PLAN_NO_C': VARCHAR, 'PLAN_NO_CODE': VARCHAR, 'TRIMMING_WIDTH_RANGE': VARCHAR, 'TRIMMING_WIDTH_CODE': VARCHAR,
            'IN_MAT_DIV_CODE': VARCHAR, 'IN_MAT_DIV': VARCHAR, 'PRODUCE_NICK_FLAG': VARCHAR, 'PROD_CAT_CODE': VARCHAR,
            'PROD_CAT': VARCHAR, 'REC_CREATOR': VARCHAR, 'REC_CREATE_TIME': VARCHAR, 'REC_REVISOR': VARCHAR,'REC_REVISOR_TIME': VARCHAR
            }

def getConnectionDb2(host, port, dbname, user, password):
    # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
    engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                           encoding="utf-8", poolclass=NullPool)
    return engine.connect()

def save_as_table(df,table_name,conn,schema_name=None,dtypedict=None):
    return pd.io.sql.to_sql(df, table_name, conn,schema_name,if_exists='append',index=False,chunksize=10000,dtype=dtypedict)

def toTIMSIJ4GR(unit,p_date):
    # 先删除这段时间的数据
    del_sql = """delete from IMSIJ4.TIMSIJ4GR WHERE DATE =  '""" + p_date + """' AND ITEM_ENAME IN ('PLAN_COSTING','ACT_COSTING') AND UNIT_CODE = '""" + unit + """'"""
    conn_db2.execute(del_sql)

    sql_plan = """SELECT 'BGTARAS1' AS REC_CREATOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME
                  ,'BGTARAS1' AS REC_REVISOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISE_TIME
                  ,' ' AS ARCHIVE_FLAG,' ' AS ARCHIVE_STAMP_NO,UNIT_CODE,'""" + p_date + """' AS DATE,' ' AS ITEM_CODE,0 AS ITEM_SEQ_NO,
                  'PLAN_COSTING' AS ITEM_ENAME,'计划成本' AS ITEM_CNAME,' ' AS ITEM_TYPE,char(cast(PLAN_COSTING as decimal(20,6))) AS ITEM_VALUE,
                  cast(PLAN_COSTING as decimal(20,6)) AS ITEM_VALUE_N
                  FROM (
                  SELECT UNIT_CODE,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*STD_TON_STEEL_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS PLAN_COSTING
                   from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                where DATA_TYPE='D' AND '1001' = ACCT 
                AND to_char(TO_TIMESTAMP(LEFT('""" + p_date + """',8)||'220000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'220000' <= PRODUCE_TIME and PRODUCE_TIME <
                '""" + p_date + """'||'220000' AND UNIT_CODE='""" + unit + """'
                GROUP BY COST_CENTER,UNIT_CODE)
            """
    print(sql_plan)
    df_plan = util.query(conn_rds, sql_plan)
    util.insert(df_plan, conn_db2, "TIMSIJ4GR", "IMSIJ4", dtypedict)

    sql_fact = """SELECT 'BGTARAS1' AS REC_CREATOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME
              ,'BGTARAS1' AS REC_REVISOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISE_TIME
              ,' ' AS ARCHIVE_FLAG,' ' AS ARCHIVE_STAMP_NO,UNIT_CODE,'""" + p_date + """'  AS DATE,' ' AS ITEM_CODE,0 AS ITEM_SEQ_NO,
              'ACT_COSTING' AS ITEM_ENAME,'实际成本' AS ITEM_CNAME,' ' AS ITEM_TYPE,char(cast(ACT_COSTING as decimal(20,6))) AS ITEM_VALUE,
              cast(ACT_COSTING as decimal(20,6)) AS ITEM_VALUE_N
              FROM (
              SELECT UNIT_CODE,
              CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE
              CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
            CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*TON_STEEL_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS ACT_COSTING
             from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                    where DATA_TYPE='D' AND '1001' = ACCT 
                    AND to_char(TO_TIMESTAMP(LEFT('""" + p_date + """',8)||'220000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'220000' <= PRODUCE_TIME and PRODUCE_TIME <
                    '""" + p_date + """'||'220000' AND UNIT_CODE='""" + unit + """'
                    GROUP BY COST_CENTER,UNIT_CODE)
                """
    print(sql_fact)
    df_fact = util.query(conn_rds, sql_fact)
    util.insert(df_fact, conn_db2, "TIMSIJ4GR", "IMSIJ4", dtypedict)



def toTIMSIJ4GQ(unit,p_date):
    # 先删除这段时间的数据
    del_sql = """delete from IMSIJ4.TIMSIJ4GQ WHERE DATE =  '""" + p_date + """' AND ITEM_ENAME IN ('PLAN_COSTING','ACT_COSTING') AND UNIT_CODE = '""" + unit + """'"""
    conn_db2.execute(del_sql)

    sql_plan = """SELECT 'BGTARAS1' AS REC_CREATOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME
                  ,'BGTARAS1' AS REC_REVISOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISE_TIME
                  ,' ' AS ARCHIVE_FLAG,' ' AS ARCHIVE_STAMP_NO,UNIT_CODE,'""" + p_date + """' AS DATE,' ' AS ITEM_CODE,0 AS ITEM_SEQ_NO,
                  'PLAN_COSTING' AS ITEM_ENAME,'计划成本' AS ITEM_CNAME,' ' AS ITEM_TYPE,char(cast(PLAN_COSTING as decimal(20,6))) AS ITEM_VALUE,
                  cast(PLAN_COSTING as decimal(20,6)) AS ITEM_VALUE_N
                  FROM (
                  SELECT UNIT_CODE,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*STD_TON_STEEL_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS PLAN_COSTING
                   from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                where DATA_TYPE='D' AND '1001' = ACCT 
                AND to_char(TO_TIMESTAMP(LEFT('""" + p_date + """',8)||'220000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'220000' <= PRODUCE_TIME and PRODUCE_TIME <
                '""" + p_date + """'||'220000' AND UNIT_CODE='""" + unit + """'
                GROUP BY COST_CENTER,UNIT_CODE)
            """
    print(sql_plan)
    df_plan = util.query(conn_rds, sql_plan)
    util.insert(df_plan, conn_db2, "TIMSIJ4GQ", "IMSIJ4", dtypedict)

    sql_fact = """SELECT 'BGTARAS1' AS REC_CREATOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME
              ,'BGTARAS1' AS REC_REVISOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISE_TIME
              ,' ' AS ARCHIVE_FLAG,' ' AS ARCHIVE_STAMP_NO,UNIT_CODE,'""" + p_date + """'  AS DATE,' ' AS ITEM_CODE,0 AS ITEM_SEQ_NO,
              'ACT_COSTING' AS ITEM_ENAME,'实际成本' AS ITEM_CNAME,' ' AS ITEM_TYPE,char(cast(ACT_COSTING as decimal(20,6))) AS ITEM_VALUE,
              cast(ACT_COSTING as decimal(20,6)) AS ITEM_VALUE_N
              FROM (
              SELECT UNIT_CODE,
              CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE
              CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
            CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*TON_STEEL_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS ACT_COSTING
             from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                    where DATA_TYPE='D' AND '1001' = ACCT 
                    AND to_char(TO_TIMESTAMP(LEFT('""" + p_date + """',8)||'220000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'220000' <= PRODUCE_TIME and PRODUCE_TIME <
                    '""" + p_date + """'||'220000' AND UNIT_CODE='""" + unit + """'
                    GROUP BY COST_CENTER,UNIT_CODE)
                """
    print(sql_fact)
    df_fact = util.query(conn_rds, sql_fact)
    util.insert(df_fact, conn_db2, "TIMSIJ4GQ", "IMSIJ4", dtypedict)


def toCD0101(unit,start_time,end_time,p_data_type):
    # 先删除这段时间的数据
    del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_CD0101 WHERE PRODUCE_TIME BETWEEN  '""" + str(start_time) + """' and  '""" + str(end_time) + """' AND UNIT_CODE = '""" + unit + """'"""
    conn_rds.execute(del_sql)

    sql = """select ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,ENTRY_COILNO,PROD_COILNO,PRODUCE_TIME
                  ,WIDTH,WIDTH_CODE,THICK,THICK_CODE,STEELNO AS ST_NO
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                                +(CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2) IN ('15') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) - SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(COST_SUBJECT_PRICE,0) ELSE 0 END))
                                END AS ACT_TOTAL_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                                +(CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2) IN ('15') THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) - SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(CASE WHEN COST_SUBJECT_STD_AMT=0 THEN 0 ELSE PROC_STD_AMT/COST_SUBJECT_STD_AMT END,0) ELSE 0 END))
                                END AS TARGET_TOTAL_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                  ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                  +(CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2) IN ('15') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) - SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(COST_SUBJECT_PRICE,0) ELSE 0 END))
                  END AS ACT_CHANGE_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                  ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                  +(CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2) IN ('15') THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) - SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(CASE WHEN COST_SUBJECT_STD_AMT=0 THEN 0 ELSE PROC_STD_AMT/COST_SUBJECT_STD_AMT END,0) ELSE 0 END))
                  END AS TARGET_CHANGE_COST
                  ,0.0 AS ACT_REVISE_COST
                  ,0.0 AS TARGET_REVISE_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2) IN ('15') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) - SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(COST_SUBJECT_PRICE,0) ELSE 0 END) END AS MAIN_MATERIAL_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('4','5') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) END AS ENERGYCONSUM_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('6') AND LEFT(COST_SUBJECT,2) NOT IN ('64') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) END AS ACCES_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('3') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) END AS ROLL_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2) NOT IN ('15') AND LEFT(COST_SUBJECT,1) IN ('1') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) END AS OTHER_COST
                  ,0.0 AS COST_6
                  ,0.0 AS COST_7
                  ,0.0 AS COST_8,
                  'BGTARAS1' AS REC_CREATOR,
                  TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                  '' AS REC_REVISOR,
                  TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                where DATA_TYPE='""" + p_data_type + """' AND '1001' = ACCT 
                AND PRODUCE_TIME >=  '""" + str(start_time) + """' AND PRODUCE_TIME<='""" + str(end_time) + """' AND APPTHROWAIMODE LIKE '%PN%'
                AND UNIT_CODE = '""" + unit + """'
                GROUP BY ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,ENTRY_COILNO,PROD_COILNO,PRODUCE_TIME
                  ,WIDTH,WIDTH_CODE,THICK,THICK_CODE,STEELNO
                """
    print(sql)
    df_plan = util.query(conn_rds, sql)
    util.insert(df_plan, conn_rds, "T_ADS_FACT_SICB_CD0101","BGTARAS1",  dtypedict)


def toCD0102(unit,p_date,p_data_type):
    # 先删除这段时间的数据
    del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_CD0102 WHERE DATE = '""" + p_date + """' AND UNIT_CODE = '""" + unit + """' """
    conn_rds.execute(del_sql)

    sql = """select ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,CLASS,
                '""" + p_date + """'  AS DATE
                ,WIDTH,WIDTH_CODE,THICK,THICK_CODE
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS ACT_TOTAL_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS TARGET_TOTAL_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS ACT_CHANGE_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS TARGET_CHANGE_COST,
                'BGTARAS1' AS REC_CREATOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                '' AS REC_REVISOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                where DATA_TYPE='""" + p_data_type + """' AND '1001' = ACCT AND '""" + unit + """' = UNIT_CODE
                AND  to_char(TO_TIMESTAMP('""" + p_date + """'||'200000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'200000' <= PRODUCE_TIME and PRODUCE_TIME <
                '""" + p_date + """'||'200000'
                GROUP BY ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,CLASS,WIDTH,WIDTH_CODE,THICK,THICK_CODE
                    """
    print(sql)
    df_plan = util.query(conn_rds, sql)
    util.insert(df_plan, conn_rds, "T_ADS_FACT_SICB_CD0102", "BGTARAS1", dtypedict)


def toCD0103(unit,p_date,p_data_type):
    # 先删除这段时间的数据
    del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_CD0103 WHERE DATE = '""" + p_date + """' AND UNIT_CODE = '""" + unit + """' """
    conn_rds.execute(del_sql)

    sql = """select ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,CLASS,
                '""" + p_date + """'  AS DATE
                ,WIDTH,WIDTH_CODE,THICK,THICK_CODE,STEELNO AS ST_NO
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS ACT_TOTAL_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS TARGET_TOTAL_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS ACT_CHANGE_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS TARGET_CHANGE_COST,
                'BGTARAS1' AS REC_CREATOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                '' AS REC_REVISOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                where DATA_TYPE='""" + p_data_type + """' AND '1001' = ACCT AND '""" + unit + """' = UNIT_CODE
                AND  to_char(TO_TIMESTAMP('""" + p_date + """'||'200000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'200000' <= PRODUCE_TIME and PRODUCE_TIME <
                '""" + p_date + """'||'200000'
                GROUP BY ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,CLASS,WIDTH,WIDTH_CODE,THICK,THICK_CODE,STEELNO
                    """
    print(sql)
    df_plan = util.query(conn_rds, sql)
    util.insert(df_plan, conn_rds, "T_ADS_FACT_SICB_CD0103", "BGTARAS1", dtypedict)

def toCD0104(unit,p_date,p_data_type):
    # 先删除这段时间的数据
    del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_CD0104 WHERE DATE = '""" + p_date + """' AND UNIT_CODE = '""" + unit + """' """
    conn_rds.execute(del_sql)

    sql = """select ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,
                '""" + p_date + """'  AS DATE
                ,WIDTH,WIDTH_CODE,THICK,THICK_CODE,STEELNO AS ST_NO
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS ACT_TOTAL_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS TARGET_TOTAL_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS ACT_CHANGE_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS TARGET_CHANGE_COST,
                'BGTARAS1' AS REC_CREATOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                '' AS REC_REVISOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                where DATA_TYPE='""" + p_data_type + """' AND '1001' = ACCT AND '""" + unit + """' = UNIT_CODE
                AND  to_char(TO_TIMESTAMP('""" + p_date + """'||'200000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'200000' <= PRODUCE_TIME and PRODUCE_TIME <
                '""" + p_date + """'||'200000'
                GROUP BY ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,WIDTH,WIDTH_CODE,THICK,THICK_CODE,STEELNO
                    """
    print(sql)
    df_plan = util.query(conn_rds, sql)
    util.insert(df_plan, conn_rds, "T_ADS_FACT_SICB_CD0104", "BGTARAS1", dtypedict)

def toCD0105(unit,p_date,p_data_type):
    # 先删除这段时间的数据
    del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_CD0105 WHERE DATE = '""" + p_date + """' AND UNIT_CODE = '""" + unit + """' """
    conn_rds.execute(del_sql)

    sql_all = """select ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,'全部' AS CLASS,
                '""" + p_date + """'  AS DATE
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS ACT_TOTAL_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS TARGET_TOTAL_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS ACT_CHANGE_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS TARGET_CHANGE_COST,
                'BGTARAS1' AS REC_CREATOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                '' AS REC_REVISOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                where DATA_TYPE='""" + p_data_type + """' AND '1001' = ACCT AND '""" + unit + """' = UNIT_CODE
                AND  to_char(TO_TIMESTAMP('""" + p_date + """'||'200000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'200000' <= PRODUCE_TIME and PRODUCE_TIME <
                '""" + p_date + """'||'200000'
                GROUP BY ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE
                    """
    print(sql_all)
    df_all = util.query(conn_rds, sql_all)
    util.insert(df_all, conn_rds, "T_ADS_FACT_SICB_CD0105", "BGTARAS1", dtypedict)

    sql_class = """select ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,CLASS,
                    '""" + p_date + """'  AS DATE
                    ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                    CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                     END AS ACT_TOTAL_COST
                    ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                    CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                     END AS TARGET_TOTAL_COST
                    ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS ACT_CHANGE_COST
                    ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS TARGET_CHANGE_COST,
                    'BGTARAS1' AS REC_CREATOR,
                    TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                    '' AS REC_REVISOR,
                    TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                    from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                    where DATA_TYPE='""" + p_data_type + """' AND '1001' = ACCT AND '""" + unit + """' = UNIT_CODE
                    AND  to_char(TO_TIMESTAMP('""" + p_date + """'||'200000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'200000' <= PRODUCE_TIME and PRODUCE_TIME <
                    '""" + p_date + """'||'200000'
                    GROUP BY ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,CLASS
                        """
    print(sql_class)
    df_class = util.query(conn_rds, sql_class)
    util.insert(df_class, conn_rds, "T_ADS_FACT_SICB_CD0105", "BGTARAS1", dtypedict)


def toCD0201(unit_code,start_time,end_time,p_data_type):
    # 先删除这段时间的数据
    del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_CD0201 WHERE UNIT_CODE='""" + str(unit_code) + """' and PRODUCE_TIME BETWEEN '""" + str(start_time) + """' AND '""" + str(end_time) + """'"""
    conn_rds.execute(del_sql)

    sql_rds = """
        SELECT
                    A.ACCT AS ACCT,
                    A.COST_CENTER,
                    A.UNIT_CODE,
                    A.CLASS,
                   A.SHIFT,
                   A.PRODUCE_TIME,
                   A.PROD_COILNO,
                    A.ENTRY_COILNO,A.STEELNO,
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)<>'15' THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)
                    +(CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,2) IN ('15') THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) - SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.COST_SUBJECT_PRICE,0) ELSE 0 END))
                    END AS ACT_TOTAL_COST,
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,2) IN ('15') THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) - SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.COST_SUBJECT_PRICE,0) ELSE 0 END) END AS MAIN_MATERIAL_COST,--主原料
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1) IN ('4','5') THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS ENERGYCONSUM_COST ,--能源
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1) IN ('6') THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS ACCES_COST ,--辅料
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1)='3' AND LEFT(A.COST_SUBJECT,2) NOT IN ('39') THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS ROLL_COST ,
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN A.COST_SUBJECT='20000'  THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS OTHER_COST ,--人工费
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1)='7'  THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS COST_6 ,--协力费
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN A.COST_SUBJECT IN ('71200', '71100','88781','88782','88785','88787','88788')  THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS COST_7 ,--运输费
                    0.0 AS COST_8,
                    SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) AS ACT_OUTPUT_WT,
                    SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_INPUT_WT,0) ELSE 0 END) AS ACT_INPUT_WT,
                    A.THICK,A.THICK_CODE,
                    A.WIDTH,A.WIDTH_CODE,
                    A.ANNEAL_CURVE,
                        A.TRIM_FLAG,
                        A.ENTRY_WIDTH,
                        A.ENTRY_WIDTH_CODE,
                        A.ENTRY_THICK,
                        A.ENTRY_THICK_CODE,
                        A.PLAN_NO_C,
                        A.PLAN_NO_CODE,
                        A.TRIMMING_WIDTH_RANGE,
                        A.TRIMMING_WIDTH_CODE,
                        A.IN_MAT_DIV_CODE ,
                        A.IN_MAT_DIV,
                        A.PRODUCE_NICK_FLAG,
                        A.PROD_CAT_CODE,
                        A.PROD_CAT,
                        'BGTARAS1' AS REC_CREATOR,
                        TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                        '' AS REC_REVISOR,
                        TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                FROM BGTARAS1.T_ADS_FACT_SICB_CA0001 A
                    where A.DATA_TYPE='""" + p_data_type + """'
                        AND '1001' = A.ACCT
                        AND A.PRODUCE_TIME >= '""" + start_time + """'
                        AND A.PRODUCE_TIME<= '""" + end_time + """' AND A.APPTHROWAIMODE LIKE '%PN%'AND A.UNIT_CODE='""" + unit_code + """'
                GROUP BY
                    A.ACCT,
                    A.DEPARTMENT_CODE,
                    A.COST_CENTER,
                    A.UNIT_CODE,
                    A.CLASS,A.SHIFT,
                    A.ENTRY_COILNO,
                    A.PROD_COILNO,
                    A.PRODUCE_TIME,A.WIDTH,A.WIDTH_CODE,A.THICK,A.THICK_CODE,A.STEELNO,
                    A.ANNEAL_CURVE,
                        A.TRIM_FLAG,
                        A.ENTRY_WIDTH,
                        A.ENTRY_WIDTH_CODE,
                        A.ENTRY_THICK,
                        A.ENTRY_THICK_CODE,
                        A.PLAN_NO_C,
                        A.PLAN_NO_CODE,
                        A.TRIMMING_WIDTH_RANGE,
                        A.TRIMMING_WIDTH_CODE,
                        A.PLAN_NO_CODE,
                        A.IN_MAT_DIV_CODE,
                        A.IN_MAT_DIV,
                        A.PRODUCE_NICK_FLAG,
                        A.PROD_CAT_CODE,
                        A.PROD_CAT
    """
    # print(sql)
    df = util.query(conn_rds, sql_rds)
    dtypedict_this = op.get_dict_type(df)
    util.insert(df, conn_rds, "T_ADS_FACT_SICB_CD0201", "BGTARAS1", dtypedict_this)

def toTTMSIJ4G7(unit,p_date):
    import datetime
    sql_str = """
                select 'BGTARAS1' AS REC_CREATOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME
                  ,'BGTARAS1' AS REC_REVISOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISE_TIME
                  ,'' AS ARCHIVE_FLAG,'' AS ARCHIVE_STAMP_NO,UNIT_CODE,'""" + p_date + """' AS DATE
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*STD_TON_STEEL_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS PLAN_COSTING
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE
                  CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*TON_STEEL_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS ACT_COSTING
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)=0 OR SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END)=0
                  THEN 0 ELSE 
                  (CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)+CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*TON_STEEL_PRICE ELSE 0 END) AS DOUBLE))
                  /(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END)+CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*STD_TON_STEEL_PRICE ELSE 0 END) AS DOUBLE))*100-100 END AS DIFF_COSTING
                  ,'' AS SPARE_ITEM_1,'' AS SPARE_ITEM_2
                  ,0.0 AS SPARE_ITEM_N1,0.0 AS SPARE_ITEM_N2,0.0 AS SPARE_ITEM_N3,0.0 AS SPARE_ITEM_N4
                  ,'' AS SPARE_REMARK,'' AS SPARE_TIME_1,'' AS SPARE_TIME_2,'' AS VERSION_NO
                from BGTARAS1.T_ADS_FACT_SICB_CA0001 
                where DATA_TYPE='D' AND '1001' = ACCT 
                        AND to_char(TO_TIMESTAMP(LEFT('""" + p_date + """',8)||'220000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'220000' <= PRODUCE_TIME and PRODUCE_TIME <
                        '""" + p_date + """'||'220000' AND UNIT_CODE='""" + unit + """'
                GROUP BY COST_CENTER,UNIT_CODE;
            """
    df = util.query(conn_rds, sql_str)
    if datetime.datetime.now().strftime("%H%M") < '0730':
        del_sql = """DELETE FROM TMSIJ4.TTMSIJ4G7 WHERE '""" + unit + """' = UNIT_CODE AND '""" + p_date + """'=DATE"""
        util.insert(df, conn_db2, "TTMSIJ4G7", "TMSIJ4", dtypedict)
    else:
        select_sql = """select count(*) from TMSIJ4.TTMSIJ4G7_R WHERE '""" + unit + """' = UNIT_CODE AND '""" + p_date + """'=DATE"""
        df_sel = util.query(conn_db2, select_sql)
        if df_sel.__len__()<0:
            del_sql = """DELETE FROM TMSIJ4.TTMSIJ4G7_R WHERE '""" + unit + """' = UNIT_CODE AND '""" + p_date + """'=DATE"""
            util.insert(df, conn_db2, "TTMSIJ4G7_R", "TMSIJ4", dtypedict)
        else:
            for tup in zip(df['plan_costing','act_costing']):
                update_str = """update TMSIJ4.TTMSIJ4G7_R set plan_costing="""+str(tup[0])+""",act_costing="""+str(tup[1])+""" where  '""" + unit + """' = UNIT_CODE AND '""" + p_date + """'=DATE"""
                conn_db2.execute(update_str)

def toTTMSIJ4G9(unit,p_date,team):
    print("---月数据计算开始---")
    #月累计
    numm_sql = """SELECT COUNT(1) AS NUM FROM TMSIJ4.TTMSIJ4G9 WHERE '""" + unit + """' = UNIT_CODE AND LEFT('""" + p_date + """',6)=DATE 
            AND CASE WHEN '""" + team + """'='甲' THEN 'A' 
            WHEN '""" + team + """'='乙' THEN 'B' 
            WHEN '""" + team + """'='丙' THEN 'C'
            WHEN '""" + team + """'='丁' THEN 'D' END=SHIFT_GROUP"""
    print(numm_sql)
    numm = util.query(conn_db2, numm_sql)

    if numm.__len__()>0:
        select_str = """
            SELECT
                CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST(SUM(COALESCE(A.UNIT_PROC_ON_AMT,0)) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END
                AS pt1,
                CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST (SUM(COALESCE(A.UNIT_PROC_STD_AMT,0)) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END
                AS pt2,
                CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1) IN ('4','5') THEN COALESCE(A.COST_SUBJECT_ON_AMT,0)*COALESCE(B.INDEX_VALUE,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END
                AS pt3,
                CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1) IN ('4','5') THEN COALESCE(A.PROC_STD_AMT,0)*COALESCE(B.INDEX_VALUE,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END
                AS pt4
                FROM BGTARAS1.T_ADS_FACT_SICB_CA0001 AS A LEFT JOIN BGTARAS1.T_ADS_WH_SICB_TR0020 AS B
                  ON A.ACCT = B.ACCT AND A.COST_CENTER = B.COST_CENTER AND A.UNIT_CODE = B.UNIT_CODE AND A.COST_SUBJECT = B.COST_SUBJECT
                where A.DATA_TYPE='D' AND '1001'= A.ACCT AND '""" + unit + """'= A.UNIT_CODE AND '""" + team + """'=A.CLASS
                AND to_char(TO_TIMESTAMP(LEFT('""" + p_date + """',6)||'01200000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'200000' <= A.PRODUCE_TIME 
                and A.PRODUCE_TIME < '""" + p_date + """'||'200000' --AND BGTARAS1.F_GROUP_NAME(PRODUCE_TIME)=CLASS
                GROUP BY A.COST_CENTER,A.UNIT_CODE,A.CLASS
                
        """
        print(select_str)
        sel_df_1 = util.query(conn_rds, select_str)
        for tup in zip(sel_df_1['pt1'], sel_df_1['pt2'], sel_df_1['pt3'], sel_df_1['pt4']):
            update_str = """
                UPDATE TMSIJ4.TTMSIJ4G9
                    SET PT_STEEL_COST_M = """+str(tup[0])+""",
                        PT_STEEL_COST_P = """+str(tup[1])+""",
                        PT_STEEL_CONSUME_M = """+str(tup[2])+""",
                        PT_STEEL_CONSUME_P = """+str(tup[3])+""",
                        REC_REVISOR ='BGTARAS1',
                        REC_REVISE_TIME = TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') 
                    WHERE '""" + unit + """' = UNIT_CODE AND LEFT('""" + p_date + """',6)=DATE AND CASE WHEN '""" + team + """'='甲' THEN 'A' 
                            WHEN '""" + team + """'='乙' THEN 'B' 
                            WHEN '""" + team + """'='丙' THEN 'C'
                            WHEN '""" + team + """'='丁' THEN 'D' END=SHIFT_GROUP
            """
            print(update_str)
            conn_db2.execute(update_str)
    else:
        insert_sql = """
            select 'BGTARAS1' AS REC_CREATOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME
              ,'BGTARAS1' AS REC_REVISOR,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISE_TIME
              ,'' AS ARCHIVE_FLAG,'' AS ARCHIVE_STAMP_NO,A.UNIT_CODE
              ,0.0 AS PDRC_PER_HOUR,0.0 AS PDRC_PER_HOUR_M,0.0 AS PDRC_PER_HOUR_P
              ,0.0 AS TOTAL_PRODUCE_WT,0.0 AS TOTAL_PRODUCE_WT_M,0.0 AS TOTAL_PRODUCE_WT_P
              ,0.0 AS PT_STEEL_CONSUME
              ,CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
              CAST (SUM(CASE WHEN LEFT(A.COST_SUBJECT,1) IN ('4','5') THEN COALESCE(A.COST_SUBJECT_ON_AMT,0)*COALESCE(B.INDEX_VALUE,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END AS PT_STEEL_CONSUME_M
              ,CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
              CAST (SUM(CASE WHEN LEFT(A.COST_SUBJECT,1) IN ('4','5') THEN COALESCE(A.PROC_STD_AMT,0)*COALESCE(B.INDEX_VALUE,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END AS PT_STEEL_CONSUME_P
              ,0.0 AS PT_STEEL_COST
              ,CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
              CAST (SUM(COALESCE(A.UNIT_PROC_ON_AMT,0)) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END AS PT_STEEL_COST_M
              ,CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
              CAST (SUM(COALESCE(A.UNIT_PROC_STD_AMT,0)) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END AS PT_STEEL_COST_P
              ,0.0 AS PT_STEEL_MTC_COST
              ,'' AS SHIFT_NO
              ,CASE WHEN A.CLASS='甲' THEN 'A' 
                    WHEN A.CLASS='乙' THEN 'B' 
                    WHEN A.CLASS='丙' THEN 'C'
                    WHEN A.CLASS='丁' THEN 'D' END AS SHIFT_GROUP,LEFT('""" + p_date + """',6) AS DATE
              ,'' AS SPARE_ITEM_1,'' AS SPARE_ITEM_2
              ,0.0 AS SPARE_ITEM_N1,0.0 AS SPARE_ITEM_N2,0.0 AS SPARE_ITEM_N3,0.0 AS SPARE_ITEM_N4
              ,'' AS SPARE_REMARK,'' AS SPARE_TIME_1,'' AS SPARE_TIME_2,'' AS VERSION_NO,0.0 AS TOTAL_PRODUCE_WT_DP
            from BGTARAS1.T_ADS_FACT_SICB_CA0001 AS A LEFT JOIN BGTARAS1.T_ADS_WH_SICB_TR0020 AS B
            ON A.ACCT = B.ACCT AND A.COST_CENTER = B.COST_CENTER AND A.UNIT_CODE = B.UNIT_CODE AND A.COST_SUBJECT = B.COST_SUBJECT
            where A.DATA_TYPE='D' AND '1001'= A.ACCT AND '""" + unit + """'= A.UNIT_CODE AND '""" + team + """'=A.CLASS
            AND to_char(TO_TIMESTAMP(LEFT('""" + p_date + """',6)||'01200000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'200000' <= A.PRODUCE_TIME 
            and A.PRODUCE_TIME < '""" + p_date + """'||'200000'
            GROUP BY A.COST_CENTER,A.UNIT_CODE,A.CLASS
        """
        print(insert_sql)
        df = util.query(conn_rds, insert_sql)
        dtypedict_df = op.get_dict_type(df)
        util.insert(df, conn_db2, "TTMSIJ4G9", "TMSIJ4", dtypedict_df)
    print("---月数据计算结束---")

    print("---日数据计算开始---")
    #日数据
    numd_sql = """SELECT COUNT(1) AS NUMD FROM TMSIJ4.TTMSIJ4G9 WHERE '""" + unit + """' = UNIT_CODE AND DATE=TO_CHAR(TO_DATE('""" + p_date + """','YYYYMMDD')+(7-DAYOFWEEK_ISO(TO_TIMESTAMP('""" + p_date + """'||'120000','YYYYMMDDHH24MISS'))) DAY,'YYYYMMDD')"""
    numd = util.query(conn_db2, numd_sql)
    if numd.__len__() < 0:
        insert_d_str = """
            insert into TMSIJ4.TTMSIJ4G9 VALUES (
               'BGTARAS1'
              ,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') 
              ,'BGTARAS1'
              ,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') 
              ,'' -- ARCHIVE_FLAG - VARCHAR(1)
              ,'' -- ARCHIVE_STAMP_NO - VARCHAR(10)
              ,'""" + unit + """' -- UNIT_CODE - VARCHAR(4)
              ,0.0 -- PDRC_PER_HOUR - DECIMAL(8, 3)
              ,0.0 -- PDRC_PER_HOUR_M - DECIMAL(8, 3)
              ,0.0 -- PDRC_PER_HOUR_P - DECIMAL(8, 3)
              ,0.0 -- TOTAL_PRODUCE_WT - DECIMAL(10, 3)
              ,0.0 -- TOTAL_PRODUCE_WT_M - DECIMAL(10, 3)
              ,0.0 -- TOTAL_PRODUCE_WT_P - DECIMAL(10, 3)
              ,0.0 -- PT_STEEL_CONSUME - DECIMAL(7, 3)
              ,0.0 -- PT_STEEL_CONSUME_M - DECIMAL(7, 3)
              ,0.0 -- PT_STEEL_CONSUME_P - DECIMAL(7, 3)
              ,0.0 -- PT_STEEL_COST - DECIMAL(9, 3)
              ,0.0 -- PT_STEEL_COST_M - DECIMAL(9, 3)
              ,0.0 -- PT_STEEL_COST_P - DECIMAL(9, 3)
              ,0.0 -- PT_STEEL_MTC_COST - DECIMAL(9, 3)
              ,'' -- SHIFT_NO - VARCHAR(1)
              ,CASE WHEN '""" + team + """'='甲' THEN 'A' 
                    WHEN '""" + team + """'='乙' THEN 'B' 
                    WHEN '""" + team + """'='丙' THEN 'C'
                    WHEN '""" + team + """'='丁' THEN 'D' END -- SHIFT_GROUP - VARCHAR(1)
              ,TO_CHAR(TO_DATE('""" + p_date + """','YYYYMMDD')+(7-DAYOFWEEK_ISO(TO_TIMESTAMP('""" + p_date + """'||'120000','YYYYMMDDHH24MISS'))) DAY,'YYYYMMDD') -- DATE - VARCHAR(8)
              ,'' -- SPARE_ITEM_1 - VARCHAR(10)
              ,'' -- SPARE_ITEM_2 - VARCHAR(10)
              ,0.0 -- SPARE_ITEM_N1 - DECIMAL(15, 2)
              ,0.0 -- SPARE_ITEM_N2 - DECIMAL(15, 2)
              ,0.0 -- SPARE_ITEM_N3 - DECIMAL(15, 3)
              ,0.0 -- SPARE_ITEM_N4 - DECIMAL(15, 3)
              ,'' -- SPARE_REMARK - VARCHAR(50)
              ,'' -- SPARE_TIME_1 - VARCHAR(14)
              ,'' -- SPARE_TIME_2 - VARCHAR(14)
              ,'' -- VERSION_NO - VARCHAR(10)
              ,0.0 --TOTAL_PRODUCE_WT_DP -DECIMAL(10, 3)
            )
        """
        select_d_str_1 = """
            SELECT CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST(SUM(COALESCE(A.UNIT_PROC_ON_AMT,0)) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END
                  AS PT1,
                  CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST(SUM(COALESCE(A.UNIT_PROC_STD_AMT,0)) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END
                  AS PT2,
                  CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1) IN ('4','5') THEN COALESCE(A.COST_SUBJECT_ON_AMT,0)*COALESCE(B.INDEX_VALUE,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END
                  AS PT3,
                  CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)=0 THEN 0 ELSE 
                  CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1) IN ('4','5') THEN COALESCE(A.PROC_STD_AMT,0)*COALESCE(B.INDEX_VALUE,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) END
                  AS PT4
                FROM BGTARAS1.T_ADS_FACT_SICB_CA0001 AS A LEFT JOIN BGTARAS1.T_ADS_WH_SICB_TR0020 AS B 
                  ON A.ACCT = B.ACCT AND A.COST_CENTER = B.COST_CENTER AND A.UNIT_CODE = B.UNIT_CODE AND A.COST_SUBJECT = B.COST_SUBJECT
                where A.DATA_TYPE='D' AND '1001'= A.ACCT AND '""" + unit + """'= A.UNIT_CODE AND '""" + team + """'=A.CLASS
                AND TO_CHAR(TO_DATE('""" + p_date + """','YYYYMMDD')- DAYOFWEEK_ISO(TO_TIMESTAMP('""" + p_date + """'||'120000','YYYYMMDDHH24MISS')) DAY,'YYYYMMDD')||'200000' <= PRODUCE_TIME 
                AND PRODUCE_TIME<TO_CHAR(TO_DATE('""" + p_date + """','YYYYMMDD')+(7-DAYOFWEEK_ISO(TO_TIMESTAMP('""" + p_date + """'||'120000','YYYYMMDDHH24MISS'))) DAY,'YYYYMMDD')||'200000' 
                AND BGTARAS1.F_GROUP_NAME(PRODUCE_TIME)=A.CLASS
                GROUP BY A.COST_CENTER,A.UNIT_CODE,A.CLASS
        """
        print(select_d_str_1)
        sel_d_df_1 = util.query(conn_rds, select_d_str_1)
        for tup in zip(sel_d_df_1['pt1'], sel_d_df_1['pt2'], sel_d_df_1['pt3'], sel_d_df_1['pt4']):
            update_str_d = """
                        UPDATE TMSIJ4.TTMSIJ4G9
                            SET PT_STEEL_COST_M = """+str(tup[0])+""",
                                PT_STEEL_COST_P = """+str(tup[1])+""",
                                PT_STEEL_CONSUME_M = """+str(tup[2])+""",
                                PT_STEEL_CONSUME_P = """+str(tup[3])+""",
                                REC_REVISOR ='BGTARAS1',
                                REC_REVISE_TIME = TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') 
                            WHERE '""" + unit + """' = UNIT_CODE AND DATE=TO_CHAR(TO_DATE('""" + p_date + """','YYYYMMDD')+(7-DAYOFWEEK_ISO(TO_TIMESTAMP('""" + p_date + """'||'120000','YYYYMMDDHH24MISS'))) DAY,'YYYYMMDD') 
                            AND CASE WHEN '""" + team + """'='甲' THEN 'A' 
                            WHEN '""" + team + """'='乙' THEN 'B' 
                            WHEN '""" + team + """'='丙' THEN 'C'
                            WHEN '""" + team + """'='丁' THEN 'D' END=SHIFT_GROUP
                    """
            print(update_str_d)
            conn_db2.execute(update_str_d)

    update_d_str_2 = """
        UPDATE TMSIJ4.TTMSIJ4G9
        SET PT_STEEL_COST_M = 
          COALESCE((SELECT PT_STEEL_COST_M 
          FROM TMSIJ4.TTMSIJ4G9
          where '""" + unit + """' = UNIT_CODE AND TO_CHAR(TO_DATE('""" + p_date + """','YYYYMMDD')+(7-DAYOFWEEK_ISO(TO_TIMESTAMP('""" + p_date + """'||'120000','YYYYMMDDHH24MISS'))) DAY,'YYYYMM')=DATE AND CASE WHEN '""" + team + """'='甲' THEN 'A' 
                WHEN '""" + team + """'='乙' THEN 'B' 
                WHEN '""" + team + """'='丙' THEN 'C'
                WHEN '""" + team + """'='丁' THEN 'D' END=SHIFT_GROUP),0)
          ,PT_STEEL_CONSUME_M = 
          COALESCE((SELECT PT_STEEL_CONSUME_M 
          FROM TMSIJ4.TTMSIJ4G9
          where '""" + unit + """' = UNIT_CODE AND TO_CHAR(TO_DATE('""" + p_date + """','YYYYMMDD')+(7-DAYOFWEEK_ISO(TO_TIMESTAMP('""" + p_date + """'||'120000','YYYYMMDDHH24MISS'))) DAY,'YYYYMM')=DATE AND CASE WHEN '""" + team + """'='甲' THEN 'A' 
                WHEN '""" + team + """'='乙' THEN 'B' 
                WHEN '""" + team + """'='丙' THEN 'C'
                WHEN '""" + team + """'='丁' THEN 'D' END=SHIFT_GROUP),0)
        WHERE '""" + unit + """' = UNIT_CODE AND DATE=TO_CHAR(TO_DATE('""" + p_date + """','YYYYMMDD')+(7-DAYOFWEEK_ISO(TO_TIMESTAMP('""" + p_date + """'||'120000','YYYYMMDDHH24MISS'))) DAY,'YYYYMMDD') 
        AND CASE WHEN '""" + team + """'='甲' THEN 'A' 
                WHEN '""" + team + """'='乙' THEN 'B' 
                WHEN '""" + team + """'='丙' THEN 'C'
                WHEN '""" + team + """'='丁' THEN 'D' END=SHIFT_GROUP
    """
    print(update_d_str_2)
    conn_db2.execute(update_d_str_2)
    print("---日数据计算结束---")

def toTIMSIJ4GS(p_unit,p_date):
    # 先删除这段时间的数据
    del_sql = """delete from IMSIJ4.TIMSIJ4GS WHERE DATE =  '""" + p_date + """' AND ITEM_ENAME IN ('PLAN_COSTING','ACT_COSTING')  AND UNIT_CODE='""" + p_unit + """' """
    conn_db2.execute(del_sql)

    sql_plan = """SELECT
            'BGTARAS1'                                    AS REC_CREATOR,
            TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME ,
            'BGTARAS1'                                    AS REC_REVISOR,
            TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISE_TIME ,
            ' '                                           AS ARCHIVE_FLAG,
            ' '                                           AS ARCHIVE_STAMP_NO,
            UNIT_CODE                                          AS UNIT_CODE,
            '""" + p_date + """'                               AS DATE,
            ' '                                           AS ITEM_CODE,
            0                                             AS ITEM_SEQ_NO,
            'PLAN_COSTING'                                AS ITEM_ENAME,
            '计划成本'                                        AS ITEM_CNAME,
            ' '                                           AS ITEM_TYPE,
            CHAR(CAST(PLAN_COSTING AS DECIMAL(20,6)))     AS ITEM_VALUE,
            CAST(PLAN_COSTING AS DECIMAL(20,6))           AS ITEM_VALUE_N,
            ''                                            AS ITEM_VALUE_CONTENT,
            ''                                            AS ITEM_TO,
            ''                                            AS ITEM_FROM,
            0.00                                          AS ITEM_MIN,
            0.00                                          AS ITEM_MAX,
            0.00                                          AS ITEM_AIM,
            ' '                                           AS ITEM_LEN,
            ' '                                           AS ITEM_UNIT,
            ' '                                           AS ITEM_FLAG,
            ''                                            AS SPARE_ITEM_1,
            ''                                            AS SPARE_ITEM_2 ,
            0.0                                           AS SPARE_ITEM_N1,
            0.0                                           AS SPARE_ITEM_N2,
            0.0                                           AS SPARE_ITEM_N3,
            0.0                                           AS SPARE_ITEM_N4 ,
            ''                                            AS SPARE_REMARK,
            ''                                            AS SPARE_TIME_1,
            ''                                            AS SPARE_TIME_2
        FROM
            (
                SELECT
                    UNIT_CODE,
                    CASE
                        WHEN SUM(
                                CASE
                                    WHEN LEFT(COST_SUBJECT,2)='15'
                                    THEN COALESCE(ACT_OUTPUT_WT,0)
                                    ELSE 0
                                END)=0
                        THEN 0
                        ELSE CAST(SUM(
                                CASE
                                    WHEN LEFT(COST_SUBJECT,2)<>'15'
                                    THEN COALESCE(UNIT_PROC_STD_AMT,0)
                                    ELSE 0
                                END) AS DOUBLE)/SUM(
                                CASE
                                    WHEN LEFT(COST_SUBJECT,2)='15'
                                    THEN COALESCE(ACT_OUTPUT_WT,0)
                                    ELSE 0
                                END)+ CAST(SUM(
                                CASE
                                    WHEN LEFT(COST_SUBJECT,2)='15'
                                    AND ACT_INPUT_WT<>0
                                    THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*STD_TON_STEEL_PRICE
                                    ELSE 0
                                END) AS DOUBLE)/SUM(
                                CASE
                                    WHEN LEFT(COST_SUBJECT,2)='15'
                                    THEN COALESCE(ACT_OUTPUT_WT,0)
                                    ELSE 0
                                END)
                    END AS PLAN_COSTING
                FROM
                    BGTARAS1.T_ADS_FACT_SICB_CA0001
                WHERE
                    DATA_TYPE='D'
                AND '1001' = ACCT AND UNIT_CODE = '""" + p_unit + """'
                AND TO_CHAR(TO_TIMESTAMP(LEFT('""" + p_date + """',8)||'220000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||
                    '220000' <= PRODUCE_TIME
                AND PRODUCE_TIME < '""" + p_date + """'||'220000'
                AND UNIT_CODE<>''
                GROUP BY
                    COST_CENTER,
                    UNIT_CODE);
                    """
    print(sql_plan)
    df_plan = util.query(conn_rds, sql_plan)
    util.insert(df_plan, conn_db2, "TIMSIJ4GS", "IMSIJ4")

    sql_fact = """SELECT
            'BGTARAS1'                                    AS REC_CREATOR,
            TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME ,
            'BGTARAS1'                                    AS REC_REVISOR,
            TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISE_TIME ,
            ' '                                           AS ARCHIVE_FLAG,
            ' '                                           AS ARCHIVE_STAMP_NO,
            UNIT_CODE                                          AS UNIT_CODE,
            '""" + p_date + """'                                        AS DATE,
            ' '                                           AS ITEM_CODE,
            0                                             AS ITEM_SEQ_NO,
            'ACT_COSTING'                                 AS ITEM_ENAME,
            '实际成本'                                        AS ITEM_CNAME,
            ' '                                           AS ITEM_TYPE,
            CHAR(CAST(ACT_COSTING AS DECIMAL(20,6)))      AS ITEM_VALUE,
            CAST(ACT_COSTING AS DECIMAL(20,6))            AS ITEM_VALUE_N,
            ''                                            AS ITEM_VALUE_CONTENT,
            ''                                            AS ITEM_TO,
            ''                                            AS ITEM_FROM,
            0.00                                          AS ITEM_MIN,
            0.00                                          AS ITEM_MAX,
            0.00                                          AS ITEM_AIM,
            ' '                                           AS ITEM_LEN,
            ' '                                           AS ITEM_UNIT,
            ' '                                           AS ITEM_FLAG,
            ''                                            AS SPARE_ITEM_1,
            ''                                            AS SPARE_ITEM_2 ,
            0.0                                           AS SPARE_ITEM_N1,
            0.0                                           AS SPARE_ITEM_N2,
            0.0                                           AS SPARE_ITEM_N3,
            0.0                                           AS SPARE_ITEM_N4 ,
            ''                                            AS SPARE_REMARK,
            ''                                            AS SPARE_TIME_1,
            ''                                            AS SPARE_TIME_2
        FROM
            (
                SELECT
                    UNIT_CODE,
                    CASE
                        WHEN SUM(
                                CASE
                                    WHEN LEFT(COST_SUBJECT,2)='15'
                                    THEN COALESCE(ACT_OUTPUT_WT,0)
                                    ELSE 0
                                END)=0
                        THEN 0
                        ELSE CAST(SUM(
                                CASE
                                    WHEN LEFT(COST_SUBJECT,2)<>'15'
                                    THEN COALESCE(UNIT_PROC_ON_AMT,0)
                                    ELSE 0
                                END) AS DOUBLE)/SUM(
                                CASE
                                    WHEN LEFT(COST_SUBJECT,2)='15'
                                    THEN COALESCE(ACT_OUTPUT_WT,0)
                                    ELSE 0
                                END)+ CAST(SUM(
                                CASE
                                    WHEN LEFT(COST_SUBJECT,2)='15'
                                    AND ACT_INPUT_WT<>0
                                    THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*TON_STEEL_PRICE
                                    ELSE 0
                                END) AS DOUBLE)/SUM(
                                CASE
                                    WHEN LEFT(COST_SUBJECT,2)='15'
                                    THEN COALESCE(ACT_OUTPUT_WT,0)
                                    ELSE 0
                                END)
                    END AS ACT_COSTING
                FROM
                    BGTARAS1.T_ADS_FACT_SICB_CA0001
                WHERE
                    DATA_TYPE='D'
                AND '1001' = ACCT AND UNIT_CODE = '""" + p_unit + """'
                AND TO_CHAR(TO_TIMESTAMP(LEFT('""" + p_date + """',8)||'220000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||
                    '220000' <= PRODUCE_TIME
                AND PRODUCE_TIME < '""" + p_date + """'||'220000'
                AND UNIT_CODE<>''
                GROUP BY
                    COST_CENTER,
                    UNIT_CODE);
                """
    print(sql_fact)
    df_fact = util.query(conn_rds, sql_fact)
    util.insert(df_fact, conn_db2, "TIMSIJ4GS", "IMSIJ4")


def sicbResult(p_unit,p_day_1,p_day_2):
    global conn_db2, conn_rds
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')

    if p_unit in ("Q602","Q512","Q503","Q414"):
        #toTTMSIJ4G7(p_unit,p_day_1)
        toTTMSIJ4G9(p_unit, p_day_1, "甲")
        toTTMSIJ4G9(p_unit, p_day_1, "乙")
        toTTMSIJ4G9(p_unit, p_day_1, "丙")
        toTTMSIJ4G9(p_unit, p_day_1, "丁")
    elif p_unit in ("Q314","Q412","Q402","Q502","Q403","Q518","Q161","Q162","Q163","Q165","Q173","Q174","Q175"):
        toTIMSIJ4GS(p_unit,p_day_1)
    if p_unit in ('Q114','Q214','Q112','Q212','Q312','Q103','Q203','Q303','Q418','Q164','Q171','Q172'):
        toTIMSIJ4GR(p_unit, p_day_1)
    elif p_unit in ('Q102','Q202','Q302','Q116','Q216','Q101','C302','H050'):
        toTIMSIJ4GQ(p_unit,p_day_1)

    toCD0101(p_unit,p_day_2+"200000",p_day_1+"200000","D")
    toCD0102(p_unit, p_day_1, "D")
    toCD0103(p_unit, p_day_1, "D")
    toCD0104(p_unit, p_day_1, "D")
    toCD0105(p_unit, p_day_1, "D")
    toCD0201(p_unit, p_day_2+"200000",p_day_1+"200000", "D")
    conn_rds.close()
    conn_db2.close()

if __name__ == '__main__':
    global conn_db2, conn_rds
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    toTIMSIJ4GS('Q412','20211102')

    conn_rds.close()
    conn_db2.close()